﻿using ModelLayer;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

namespace DataAccessObjectLayer
{
    /// <summary>
    /// This class is used by RouteCtr to connect with the database
    /// </summary>
    public class DBRoute : DBClass, IDBRoute
    {
        private List<Station> stations;

        /// <summary>
        /// Builds a query string, which is later passed on to the database and executed to recieve
        /// a route
        /// </summary>
        /// <param name="wClause">The where clause.</param>
        /// <returns></returns>
        protected override string BuildQuery(string wClause)
        {
            String query = "SELECT * FROM Route";
            if (wClause.Length > 0)
            {
                query += " WHERE " + wClause;
            }
            return query;
        }

        /// <summary>
        /// Builds an object from either a dataset or a datareader.
        ///
        /// The bool 'connected' defines if it is one or the other
        /// </summary>
        /// <param name="dataSetOrDataReader"></param>
        /// <param name="connected"></param>
        protected override object BuildObject(object dataSetOrDataReader, bool connected)
        {
            Route route = null;

            if (connected)
            {
                var dataReader = dataSetOrDataReader as SqlDataReader;

                DBStation dbStation = new DBStation();

                if (dataReader != null)
                    route = new Route
                    {
                        Id = (long)dataReader["Id"],
                        StartStation = dbStation.GetStation((long)dataReader["FK_StationStart"]),
                        EndStation = dbStation.GetStation((long)dataReader["FK_StationEnd"]),
                        PartRoutes = new List<PartRoute>()
                    };
                IDBPartRoute dbPartRoute = new DBPartRoute();
                route.PartRoutes = dbPartRoute.GetAllPartRoutes(route.Id);
            }
            else
            {
                var dataRow = dataSetOrDataReader as DataRow;
                DBStation dbStation = new DBStation();

                if (dataRow != null)
                    route = new Route
                    {
                        Id = (long)dataRow["Id"],
                        StartStation = stations.FirstOrDefault(s =>
                        s.Id.Equals((long)dataRow["FK_StationStart"])),
                        EndStation = stations.FirstOrDefault(s =>
                        s.Id.Equals((long)dataRow["FK_StationEnd"])),
                        PartRoutes = new List<PartRoute>()
                    };
                IDBPartRoute dbPartRoute = new DBPartRoute();
                route.PartRoutes = dbPartRoute.GetAllPartRoutes(route.Id);
            }
            object o = route;
            return o;
        }

        /// <summary>
        /// Returns a list containing all customers in the database (MiscWhere is empty, returning
        /// all)
        /// </summary>
        public List<Route> GetAllRoutes()
        {
            DBStation dbStation = new DBStation();
            stations = dbStation.GetAllStations();
            List<Route> list = new List<Route>();
            foreach (var o in MiscWhere(""))
            {
                list.Add((Route)o);
            }
            return list;
        }

        /// <summary>
        /// Returns a route from a given id
        /// </summary>
        public Route GetRoute(long routeId)
        {
            string wClause = "id = '" + routeId + "'";
            return (Route)SingleWhere(wClause);
        }

        /// <summary>
        /// inserts a new route in the database with information coming from a route object
        /// SqlParameters are used to secure database from sql injections
        /// </summary>
        /// <param name="route">The route.</param>
        /// <param name="con">The connection which is being used to connect to the database</param>
        /// <param name="transaction">the current transaction being used by DBReservation</param>
        /// <returns>The number of affected rows.</returns>
        /// <exception cref="System.Exception">The route was not inserted</exception>
        public int InsertRoute(Route route, SqlConnection con, SqlTransaction transaction)
        {
            int nextId = GetMax.GetMaxId("SELECT MAX(Id) FROM Route");
            nextId = nextId + 1;
            int rc = -1;

            const string query = "INSERT INTO Route(Id, TotalTime, TotalDistance,FK_StationStart,FK_StationEnd)" +
                                 "VALUES(@Id, @TotalTime, @TotalDistance, @StationStart, @StationEnd)";
            try
            {
                using (var cmdIns = new SqlCommand(query, con, transaction))
                {
                    cmdIns.Parameters.Add(new SqlParameter("@Id", SqlDbType.BigInt) { Value = nextId });
                    cmdIns.Parameters.Add(new SqlParameter("@TotalTime", SqlDbType.Float)
                    {
                        Value = route.TotalTime.TotalMinutes
                    });
                    cmdIns.Parameters.Add(new SqlParameter("@TotalDistance", SqlDbType.Float)
                    {
                        Value = route.TotalDistance
                    });
                    cmdIns.Parameters.Add(new SqlParameter("@StationStart", SqlDbType.BigInt)
                    {
                        Value = route.StartStation.Id
                    });
                    cmdIns.Parameters.Add(new SqlParameter("@StationEnd", SqlDbType.BigInt)
                    {
                        Value = route.EndStation.Id
                    });
                    rc = cmdIns.ExecuteNonQuery();

                    DBPartRoute dbPartRoute = new DBPartRoute();
                    dbPartRoute.InsertPartRoutes(route.PartRoutes, nextId, con, transaction);
                }
            }
            catch
            (Exception e)
            {
                TryRollback(e, transaction, "Insert in DBRoute");
            }

            if (rc == -1)
                throw new Exception("The route was not inserted");
            //return the routeId instead of rc - this is used in another context

            return nextId;
        }

        /// <summary>
        /// Updates an existing customer in the database with information coming from a customer
        /// object SqlParameters are used to secure database from sql injections
        /// </summary>
        /// <param name="route">The route.</param>
        /// <returns>The number of affected rows.</returns>
        /// <exception cref="System.Exception">The route was not updated</exception>
        public int UpdateRoute(Route route)
        {
            int rc = -1;
            const string query = "UPDATE Route SET " +
                                 "TotalTime = @TotalTime, " +
                                 "TotalDistance = @TotalDistance " +
                                 "WHERE Id = @Id";

            var dbCon = new DBCon();

            using (SqlConnection connection = (SqlConnection)dbCon.GetConnection())
            {
                SqlTransaction transaction;
                SqlCommand cmdUpd = SqlCommand(connection, out transaction);

                try
                {
                    cmdUpd.CommandText = query;

                    using (cmdUpd)
                    {
                        cmdUpd.Parameters.Add(new SqlParameter("@Id", SqlDbType.BigInt) { Value = route.Id });
                        cmdUpd.Parameters.Add(new SqlParameter("@TotalTime", SqlDbType.Float) { Value = Convert.ToDouble(route.TotalTime.TotalMinutes) });
                        cmdUpd.Parameters.Add(new SqlParameter("@TotalDistance", SqlDbType.Float) { Value = route.TotalDistance });
                        rc = cmdUpd.ExecuteNonQuery();
                    }
                    transaction.Commit();
                }

                catch (Exception e)
                {
                    TryRollback(e, transaction, "Update in DBRoute");
                }
                if (rc == -1)
                    throw new Exception("The route was not updated");
                return rc;
            }
        }

        /// <summary>
        /// Deletes a customer from the database SqlParameters are used to secure database from sql
        /// injections
        /// </summary>
        /// <param name="routeId">The route identifier.</param>
        /// <returns>System.Int32.</returns>
        /// <exception cref="System.Exception">The route was not deleted</exception>
        public int RemoveRoute(long routeId)
        {
            int rc = -1;
            String query = "DELETE FROM Route WHERE id = " + routeId;
            var dbCon = new DBCon();

            using (SqlConnection connection = (SqlConnection)dbCon.GetConnection())
            {
                SqlTransaction transaction;
                var cmdDel = SqlCommand(connection, out transaction);

                try
                {
                    cmdDel.CommandText = query;

                    using (cmdDel)
                    {
                        cmdDel.Parameters.Add(new SqlParameter("@Id", SqlDbType.BigInt) { Value = routeId });
                        rc = cmdDel.ExecuteNonQuery();
                    }
                    transaction.Commit();
                }

                catch (Exception e)
                {
                    TryRollback(e, transaction, "Delete in DBRoute");
                }
                if (rc == -1)
                    throw new Exception("The route was not deleted");
                return rc;
            }
        }
    }
}